<?php

class yzSql extends yzCore{

	protected $aProperties = array(
		'yz.sql.context'				      => 'mysql',
	    'yz.sql.smart_quotes'                 => true,
        'yz.sql.db'                           => false, // false or name of db object to use for escape and quote (must be set)
        'yz.sql.escape'                       => true,
	    'yz.sql.default.operator'             => 'and',
		'yz.sql.default.order.dir'            => 'asc',
		'yz.sql.default.limit.low'            => 'LIMIT %d',
	    'yz.sql.default.limit.both'           => 'LIMIT %d OFFSET %d',
		'yz.sql.default.order.atom'           => ' %s %s',
		'yz.sql.default.order'                => 'ORDER BY %s',
	    'yz.sql.default.join.template'        => '%s JOIN %s %s',
        'yz.sql.default.join.atom'            => 'ON %s',
		'yz.sql.default.delete'               => 'DELETE FROM %s %s %s',
		'yz.sql.default.insert'               => 'INSERT INTO %s (%s) VALUES %s',
		'yz.sql.mysql.replace'                => 'REPLACE INTO %s (%s) VALUES %s',
		'yz.sql.default.update'               => 'UPDATE %s SET %s %s',
		'yz.sql.default.values'               => '(%s)',
		'yz.sql.default.where'                => 'WHERE %s',
		'yz.sql.default.set.atom'             => '%s = %s',
		'yz.sql.default.select.atom'          => '%s = %s',
	    'yz.sql.default.select'               => 'SELECT %1$s %6$s FROM %2$s %3$s %4$s %5$s',
		'yz.sql.default.allowed.operator'     => 'and,or,and not,or not',
		'yz.sql.default.allowed.order.dir'    => 'asc,desc',
		'yz.sql.default.quote'                => "'",
		'yz.sql.mysql.tquote'                  => '`', // table quotes
		'yz.sql.allowed.no_quotes.functions'  => 'NULL,NOW,DEFAULT,CURTIME,CURDATE',
		'yz.sql.no_quotes.remove'             => '()',
		'yz.sql.fields'                       => null, // fields; auto setted but only if null
		'yz.sql.allow.fields_overwrite'       => true,
	    'yz.sql.default.max_table_name_length'=> 31,
        'yz.sql.mysql.max_table_name_length'  => 16,
	    'yz.sql.default.join'                 => 'left',
	    'yz.sql.default.allowed.join'         => 'left,right,left outer,right outer,natural,full,cross',
	    'yz.sql.mssql.allowed.join'            => 'left,right,left outer,right outer,full,cross',

	); // array $aProperties

	public function build($sTemplateName, $aVars, $sContext = null){

		// auto - get context
		if ($sContext === null) {

			$sContext = $this->getProperty('yz.sql.context');

		} // if

		// context template (db specific)

		$sTemplate = $this->getContextProperty($sTemplateName);

		if (empty($sTemplate)){

			return null;

		} // if

		return vsprintf($sTemplate,(array)$aVars);

	} // function build

	public function sanitize($sString, $sTemplate, $bAllowEmpty = false){

		$sString = strtolower(strval($sString));

		if (($bAllowEmpty && empty($sString)) || in_array($sString,explode(',', $this->getContextProperty('allowed.'.$sTemplate)))){

            return $sString;

        // getting default
        } else {

            return $this->getContextProperty($sTemplate);

        } // if

	}

	public function extractFields($mFields){

		if (is_array($mFields)){

			$sFields = implode(',',$mFields);

		} else {

			$sFields = strval($mFields);

		} // if

		$sFields = strtolower($sFields);

		if ($this->getProperty('yz.sql.allow.fields_overwrite')==true || $this->getProperty('yz.sql.fields')==null){

			$this->setProperty('yz.sql.fields', $sFields);

		} // if

		return $sFields;

	} // function

	public function add($sWhat){

		$aArgs = func_get_args();

		unset($aArgs[0]);

		if ($sWhat!='add' && method_exists($this, $sWhat)){

			return $this->setProperty('_result.'.$sWhat, call_user_func_array(array($this, $sWhat), $aArgs));

		} else{

			return null;

		} // if

	} // function

	public function quote($mString, $sQuote = null){

	 // todo: better smart_quotes
	 if ($this->getProperty('yz.sql.smart_quotes') && is_string($mString) && (in_array(str_replace(str_split($this->getProperty('yz.sql.no_quotes.remove')),'',$mString),explode(',',$this->getProperty('yz.sql.allowed.no_quotes.functions'))) || in_array($mString,explode(',',$this->getProperty('yz.sql.fields'))))){

			return strval($mString);

		} // if

		if ($sQuote === null){

			$sQuote = $this->getContextProperty('quote');

		} // if

		if ($this->getProperty('yz.sql.db') && $this->isSetObject($this->getProperty('yz.sql.db')) && method_exists($this->getObject($this->getProperty('yz.sql.db'),'quote'))){

			return $this->getObject($this->getProperty('yz.sql.db'))->quote($mString, $sQuote);

		} else {

			if (is_array($mString)){

				return array_map(array($this,'quote'),$mString,array_fill(0,count($mString),$sQuote));

			} else {

				return $sQuote.(strval($mString)).$sQuote;

			} // if

		} // if

	} // function quote()

	public function escape($mString, $bPerformCheck = false){

		if (!$bPerformCheck || ($bPerformCheck && $this->getProperty('yz.sql.escape'))){

			if ($this->getProperty('yz.sql.db')  && $this->isSetObject($this->getProperty('yz.sql.db')) && method_exists($this->getObject($this->getProperty('yz.sql.db'),'escape'))){

				return $this->getObject($this->getProperty('yz.sql.db'))->escape($mString);

			} else {

				if (is_array($mString)){

					return array_map(array($this,'escape'),$mString, array_fill(0,count($mString),$bPerformCheck));

				} else {

					return addslashes(strval($mString));

				} // if

			} // if

		} else {

			return $mString;

		}

	} // function escape ()

	public function select($sWhat, $sTable, $mConditions, $mOrder = null, $mLimit = null, $mJoin = null){

	   $aVars = array(
            $this->escape(strval($sWhat),true),
            $this->from($sTable),
            $this->where($mConditions),
            $this->order($mOrder),
            $this->limit($mLimit),
            $this->join($mJoin)

	   );

	       return $this->build('select', $aVars);

	} // function

	public function set($aData){

		$aSetAtoms = array();

		foreach ($aData as $mKey=>$mValue){

			$aSetAtoms[] = $this->build('set.atom',array($this->escape(strval($mKey), true),$this->quote($this->escape(strval($mValue),true))));

		} // foreach

		return implode(',',$aSetAtoms);

	} // function

	public function update($sTable, $aData, $mConditions){

		$aVars = array(
				$this->from($sTable),
				$this->set($aData),
				$this->where($mConditions)
			); // array

		return $this->build('update', $aVars);

	} // function

	public function values($aValues){

	   return $this->build('values',implode(',',(array)$this->quote($this->escape((array)$aValues,true))));

	}

	public function insert($sTable, $aData, $aValues = null){

		if (is_array($aValues)){

			$aVars = array(
				$this->from($sTable),
				$this->escape($this->extractFields($aData),true),
				implode(',',array_map(array($this,'values'), $aValues))
			); // array

		} else {

			$aVars = array(
                $this->from($sTable),
                $this->escape($this->extractFields(array_keys($aData)),true),
				$this->values($aData)
			); // array

		} // if

		return $this->build('insert', $aVars);

	} // function

	public function delete($mFrom, $mConditions = null, $mLimit = null){

		$aVars = array(
			$this->from($mFrom),
			$this->where($mConditions),
			($this->getProperty('yz.sql.context')=='mysql' && intval($mLimit)>0) ? $this->limit($mLimit) : ''
		); // array

		return $this->build('delete',$aVars);

	} // function delete()

	public function replace($sTable, $aData, $aValues = null){

		if ($this->getProperty('yz.sql.context')=='mysql'){

			if (is_array($aValues)){

				$aVars = array(
					$this->from($sTable),
					$this->escape($this->extractFields($aData),true),
					implode(',',array_map(array($this,'values'), $aValues))
				); // array

			} else {

				$aVars = array(
                    $this->from($sTable),
					$this->escape($this->extractFields(array_keys($aData)),true),
					$this->values($aData)
				); // array

			} // if

			return $this->build('replace', $aVars);

		} else {

			return null;

		} // if

	} // function

	public function where($mConditions, $sOperator = null, $bTopEntry = true){

		$sOperator = $this->sanitize($sOperator,'operator',false);

		if (is_string($mConditions)){

			return $bTopEntry==true ? $this->setProperty('_result.select',$this->build('where',array($mConditions))) : $mConditions;

		} else if (is_array($mConditions)){

			foreach ($mConditions as $mKey=>$mValue){

				    if (is_array($mValue)){

				    	$aConditionsAtoms[] = $this->where($mValue, $mKey, false);

				    } else {

				    	 if (is_int($mKey)){

                            $aConditionsAtoms[] = $this->escape($mValue,true);

                         } else {

				    	       $aConditionsAtoms[] = $this->build('select.atom',array($this->escape($mKey,true),$this->quote($this->escape(strval($mValue),true))));

			            } // if

			     	} // if


			} // foreach

			return $bTopEntry==true ? $this->setProperty('_result.select',$this->build('where',array(implode(' '.$sOperator.' ',$aConditionsAtoms)))) : '('.implode(' '.$sOperator.' ',$aConditionsAtoms).')';

		} // if

	} // function

	public function limit($mRowCount = 0, $mOffset = null){

	  if ($mRowCount == null && $mOffset == null){

            return '';

        }

		$sResult	= '';

		if (func_num_args() == 1 && is_array($mRowCount)){

			$nRowCount = isset($mRowCount[0]) ? intval($mRowCount[0]) : 0;

            $nOffset = isset($mRowCount[1]) ? intval($mRowCount[1]) : 0;

		} else {

	       	$nRowCount	= intval($mRowCount);

	       	$nOffset	= intval($mOffset);

		} // if

		switch ($this->getProperty('yz.sql.context')){

			default:

				if ($nRowCount>=0 && $nOffset>0){

					$sResult = $this->build('limit.both', array($nRowCount, $nOffset));

				} else if ($nRowCount>=0) {

					$sResult =  $this->build('limit.low', array($nRowCount));

				} // if

			break; // default

		} // switch

		return $this->setProperty('_result.limit',$sResult);

	} // function

	public function order($mOrderFields, $mOrderDir=null){

		if ($mOrderFields == null && $mOrderDir == null){

			return '';

		}

		$aOrderAtoms = array();

		if (is_array($mOrderFields)){

			$mOrderFields = array_map('strval',$mOrderFields);

		} else {

			$mOrderFields = strval($mOrderFields);

		} // if

		if (is_array($mOrderFields) && $mOrderDir === null){

			foreach ($mOrderFields as $sField=>$sOrderDir){

				if (!is_string($sField)){

					$aOrderAtoms[] = $this->build('order.atom',array(strval($sOrderDir),''));

				} else {

					$aOrderAtoms[] = $this->build('order.atom',array($sField,$this->sanitize($sOrderDir,'order.dir',true)));

				} // if

			} // foreach

		} else if (is_array($mOrderFields) && is_array($mOrderDir)){

			foreach ($mOrderFields as $mKey=>$sField){

				$sOrderDir =  isset($mOrderDir[$mKey]) ? $mOrderDir[$mKey] : '';

				$aOrderAtoms[] =  $this->build('order.atom',array($sField,$this->sanitize($sOrderDir,'order.dir',true)));

			} // foreach

		} else if (is_array($mOrderFields)){

			foreach ($mOrderFields as $sField){

					$aOrderAtoms[] = $this->build('order.atom',array($sField,$this->sanitize($mOrderDir,'order.dir',true)));

			} // foreach

		} else {

			$aOrderAtoms[] = $this->build('order.atom',array($mOrderFields,$this->sanitize($mOrderDir,'order.dir',true)));

		} // if

		$sResult =  $this->build('order',array(implode(',',$aOrderAtoms)));

		return $this->setProperty('_result.order',$this->escape($sResult,true));

	} // function order()

	public function join($mJoins){

		if (func_num_args()>1){

			$aJoins = func_get_args();

		} else if (is_array($mJoins) && !is_array($mJoins[0])){

			$aJoins = array($mJoins);

		} else {

			$aJoins = array();

		} // if

		$aResult = array();

		foreach ($aJoins as $mJoin){

			if (is_array($mJoin)){

				$aVars = array(
				      strtoupper($this->sanitize($mJoin[0],'join',true)),
				      strval($mJoin[1]),
	                  $this->build('join.atom',$mJoin[2]),
	           	);

				$aResult[] = $this->build('join.template',$aVars);

			} else {

				$aResult[] = $this->escape(strval($mJoin),true);

			}

		} // foreach

		return $this->setProperty('_result.join',implode(',',$aResult));

	} // function


	/**
	 * Returns sanitized list of tables as a string
	 *
	 * @param array|string $mTable
	 * @params string ...
	 * @return string list of tables
	 */
	public function from($mTable){

		if (func_num_args()>1){

			$mTable = func_get_args();

		} // if

		if (is_array($mTable)){

			$aTables = array_map('strval',$mTable);

		} else {

			$aTables[] = strval($mTable);

		} // if

		$nMaxLength = $this->getContextProperty('max_table_name_length');

		foreach ($aTables as &$sTable){

			$sTable = substr($sTable,0,$nMaxLength);

		} // foreach

		if ($this->getProperty('yz.sql.context')=='mysql'){

			foreach ($aTables as &$sTable){

				$sTable = $this->getProperty('yz.sql.mysql.tquote').$sTable.$this->getProperty('yz.sql.mysql.tquote');

			} //

		} // if

		return  $this->setProperty('_result.tables',implode(',',$aTables));

	} // function

	public function group(){}
	public function having(){}
	public function generate(){}

} // class

?>